import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class OracleTunnedTemplate extends OracleNormalTemplate {
	public String[] tables = {"lineitem", "world_region","actual_orders","partners"};
	
	class Pair {
		int a;
		int b;
		Pair (int a, int b) {
			this.a = a;
			this.b = b;
		}
	}
	
	public int deleteData() {
		int success = 0;
		for (String table : tables) {
			if (execute("DELETE FROM " + table)) success++;
		}
		return success;
	}
	private Date lshipdate = null;
	private String rname = "";
	private String psize = "";
	private String ptype = "";
	private Date oorderdate = null;
	private Date oorderdate2 = null;
	private String cmktsegment = "";
	@Override
	public void configure(Map<String, Object> params) {		
		setConnection((Connection) params.get("connection"));
	}
	public  Map<String, Object> insertLineItems(int nInsertsLineitemExpected) throws SQLException {
		try{
			deleteIndexes();
		}catch(Exception e){
			System.out.println(e.getMessage());			
		}
		Map<String, Object> result = new HashMap<String, Object>();
		double scaleFactor = (double) nInsertsLineitemExpected/(double) CARDINALITY_LINEITEM;
		int nInsertsPartsuppExpected = (int) (scaleFactor * CARDINALITY_PARTSUPP);
		int nInsertsPartExpected = (int) (scaleFactor * CARDINALITY_PART);
		int nInsertsNationExpected = (int) (CARDINALITY_NATION);
		int nInsertsRegionExpected = (int) (CARDINALITY_REGION);
		int nInsertsCustomerExpected = (int) (scaleFactor * CARDINALITY_CUSTOMER);
		int nInsertsOrdersExpected = (int) (scaleFactor * CARDINALITY_ORDERS);
		int nInsertsSupplierExpected = (int) (scaleFactor * CARDINALITY_SUPPLIER);
		int nInsertsLineitem, nInsertsWorldRegion, nInsertsActualOrder, nInsertsPartner;
		nInsertsActualOrder = nInsertsLineitem = nInsertsWorldRegion = nInsertsPartner = 0;
		
		//Minimum 1 Region and 1 Nation
		nInsertsRegionExpected = nInsertsRegionExpected == 0 ? 1 : nInsertsRegionExpected;
		nInsertsNationExpected = nInsertsNationExpected == 0 ? 1 : nInsertsNationExpected;
		
		//Set result vars
		result.put("lineitemExpected", nInsertsLineitemExpected);
		result.put("partsuppExpected", nInsertsPartsuppExpected);
		result.put("partExpected", nInsertsPartExpected);
		result.put("nationExpected", nInsertsNationExpected);
		result.put("regionExpected", nInsertsRegionExpected);
		result.put("customerExpected", nInsertsCustomerExpected);
		result.put("ordersExpected", nInsertsOrdersExpected);
		result.put("supplierExpected", nInsertsSupplierExpected);
		
		//Set vars actual state
		int lastPkRegion = maxValue("world_region", "R_RegionKey");
		int lastPkNation = maxValue("world_region", "N_NationKey");
		int lastPkSupplier = maxValue("partners", "S_SuppKey");
		int lastPkPart = maxValue("partners", "P_PartKey");
		int lastPkOrder = maxValue("actual_orders", "O_OrderKey");
		int lastCountPartsupp = count("partners");
		int lastPkPartner = maxValue("partners", "P_PartnerKey");
		
		//World Regions
		String sInsert = "INSERT INTO world_region " +
				"(N_NationKey,N_Name,N_Comment,R_RegionKey,R_Name,R_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?)";

		PreparedStatement oPst = getConnection().prepareStatement(sInsert);
		
		int nPk = lastPkRegion + 1;
		int nPk2 = lastPkNation + 1;
		for (int i = 0; i < nInsertsRegionExpected; i++) {
			for(int j = 0; j < nInsertsNationExpected/nInsertsRegionExpected;j++){
				oPst.setInt(1, nPk2);
				oPst.setString(2, RandomValues.randomString(32));
				oPst.setString(3, RandomValues.randomString(60));
								
				oPst.setInt(4, nPk);
				oPst.setString(5, "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA");
				oPst.setString(6, RandomValues.randomString(60));
				try {
					oPst.execute();
					nInsertsWorldRegion++;
				} catch (Exception ex) {
					System.out.println(ex.getMessage());					
				}
				nPk2++;
			}
			nPk++;
		}
		//Actual Orders
		
		sInsert = "INSERT INTO actual_orders " +
				"(O_OrderKey,O_OrderStatus,O_TotalPrice,O_OrderDate,O_OrderPriority,O_Clerk,O_ShipPriority,O_Comment, C_CustKey,C_Name,C_Address,C_NationKey,C_Phone,C_AcctBal,C_MktSegment, C_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkOrder + 1;
		for (int i = 0; i < nInsertsOrdersExpected; i++) {
			oPst.setInt(1, nPk);
			oPst.setString(2, RandomValues.randomString(32));
			oPst.setDouble(3, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setDate(4, RandomValues.randomDate());
			oPst.setString(5, RandomValues.randomString(8));
			oPst.setString(6, RandomValues.randomString(32));
			oPst.setInt(7, RandomValues.randomInt(1000, 9999));
			oPst.setString(8, RandomValues.randomString(40));
			oPst.setInt(9, RandomValues.randomInt(1000, 9999));
			oPst.setString(10, RandomValues.randomString(32));
			oPst.setString(11, RandomValues.randomString(32));
			oPst.setInt(12, RandomValues.randomInt(1000, 9999));
			oPst.setString(13, RandomValues.randomString(32));
			oPst.setDouble(14, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(15, RandomValues.randomString(32));
			oPst.setString(16, RandomValues.randomString(60));
			try {
				oPst.execute();
				nInsertsActualOrder++;
			} catch (Exception ex) {
				System.out.println(ex.getMessage());
			}
			nPk++;
		}		
		
		//Partners		
		sInsert = "INSERT INTO partners " +
				"(P_PartnerKey,P_PartKey,P_Name,P_Mfgr,P_Brand,P_Type,P_Size,P_Container,P_RetailPrice,P_Comment,S_SuppKey,S_Name,S_Address,S_NationKey,S_Phone,S_AcctBal,S_Comment,PS_AvailQty,PS_SupplyCost,PS_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		ArrayList<Integer> partSuppKeys = new ArrayList<Integer>();
		oPst = getConnection().prepareStatement(sInsert);
		nPk = lastPkPart + 1;
		nPk2 = lastPkSupplier + 1;
		int nPk3 = lastPkPartner + 1;
		for (int i = 0; i < nInsertsPartExpected-nInsertsPartsuppExpected; i++) {
			oPst.setInt(1, nPk3);
			oPst.setInt(2, nPk);
			oPst.setString(3,  RandomValues.randomString(32));
			oPst.setString(4, RandomValues.randomString(32));
			oPst.setString(5, RandomValues.randomString(32));
			oPst.setString(6, RandomValues.randomString(32));
			oPst.setInt(7, RandomValues.randomInt(1000, 9999));
			oPst.setString(8, RandomValues.randomString(32));
			oPst.setDouble(9, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(10, RandomValues.randomString(32));
			oPst.setNull(11, Types.INTEGER);
			oPst.setNull(12,  Types.VARCHAR);
			oPst.setNull(13,  Types.VARCHAR);
			oPst.setNull(14, Types.INTEGER);
			oPst.setNull(15, Types.VARCHAR);
			oPst.setNull(16, Types.DOUBLE);
			oPst.setNull(17, Types.VARCHAR);
			oPst.setNull(18, Types.INTEGER);
			oPst.setNull(19, Types.DOUBLE);
			oPst.setNull(20,  Types.VARCHAR);
			try {
				oPst.execute();
				nInsertsPartner++;
			} catch (Exception ex) {
				System.out.println(ex.getMessage());			
			}
			nPk++;
			nPk3++;
		}
		for (int i = 0; i < nInsertsSupplierExpected-nInsertsPartsuppExpected; i++) {
			oPst.setInt(1, nPk3);
			oPst.setNull(2, Types.INTEGER);
			oPst.setNull(3,  Types.VARCHAR);
			oPst.setNull(4, Types.VARCHAR);
			oPst.setNull(5, Types.VARCHAR);
			oPst.setNull(6, Types.VARCHAR);
			oPst.setNull(7, Types.INTEGER);
			oPst.setNull(8, Types.VARCHAR);
			oPst.setNull(9, Types.DOUBLE);
			oPst.setNull(10, Types.VARCHAR);
			oPst.setInt(11, nPk2);
			oPst.setString(12,  RandomValues.randomString(32));
			oPst.setString(13, RandomValues.randomString(32));
			if (nInsertsWorldRegion > 0) {
				oPst.setInt(14, RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsWorldRegion));
			} else {
				oPst.setNull(14, Types.INTEGER);
			}
			oPst.setString(15, RandomValues.randomString(9));
			oPst.setDouble(16, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(17, RandomValues.randomString(53));
			oPst.setNull(18, Types.INTEGER);
			oPst.setNull(19, Types.DOUBLE);
			oPst.setNull(20,  Types.VARCHAR);
			try {
				oPst.execute();
				nInsertsPartner++;
			} catch (Exception ex) {
				System.out.println(ex.getMessage());			
			}
			nPk2++;
			nPk3++;
		}
		nPk = lastCountPartsupp + 1;
		for (int i = 0; i < nInsertsPartsuppExpected; i++) {
			oPst.setInt(1, nPk3);
			partSuppKeys.add(nPk3);
			int regsPart = (nInsertsPartExpected + lastPkPart);
			int nPk4 = (int) (Math.ceil((double) nPk / (double) regsPart));
			int nPk1 = ((nPk - 1) % regsPart)+1;
			oPst.setInt(2, nPk1);
			oPst.setString(3,  RandomValues.randomString(32));
			oPst.setString(4, RandomValues.randomString(32));
			oPst.setString(5, RandomValues.randomString(32));
			oPst.setString(6, RandomValues.randomString(32));
			oPst.setInt(7, RandomValues.randomInt(1000, 9999));
			oPst.setString(8, RandomValues.randomString(32));
			oPst.setDouble(9, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(10, RandomValues.randomString(32));
			oPst.setInt(11, nPk4);
			oPst.setString(12,  RandomValues.randomString(32));
			oPst.setString(13, RandomValues.randomString(32));
			if (nInsertsWorldRegion > 0) {
				oPst.setInt(14, RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsWorldRegion));
			} else {
				oPst.setNull(14, Types.INTEGER);
			}
			oPst.setString(15, RandomValues.randomString(9));
			oPst.setDouble(16, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(17, RandomValues.randomString(53));
			oPst.setInt(18, RandomValues.randomInt(1000, 9999));
			oPst.setDouble(19, RandomValues.randomFloat(1000, 9999, 2));
			oPst.setString(20,  RandomValues.randomString(100));
			try {
				oPst.execute();
				nInsertsPartner++;
			} catch (Exception ex) {
				System.out.println(ex.getMessage());			
			}
			nPk++;
		}
		
		//Lineitems		
		sInsert = "INSERT INTO lineitem " +
				"(L_OrderKey,L_PartnerKey,L_LineNumber,L_Quantity,L_ExtendedPrice,L_Discount,L_Tax,L_ReturnFlag,L_LineStatus,L_ShipDate,L_CommitDate,L_ReceiptDate,L_ShipInstruct,L_ShipMode,L_Comment) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,? ,? ,?)";

		oPst = getConnection().prepareStatement(sInsert);
		int nPkOrders = lastPkOrder + 1;
		for (int i = 0; i < nInsertsActualOrder; i++) {
			int nLength = partSuppKeys.size();
			for (int j=0; j < nLength; j++) {
				int partSuppKey =partSuppKeys.get(j);
				oPst.setInt(1, nPkOrders);
				oPst.setInt(2, partSuppKey);
				oPst.setInt(3, nPkOrders % 5);
				oPst.setInt(4, RandomValues.randomInt(1000, 9999));
				oPst.setDouble(5, RandomValues.randomFloat(1000, 9999, 2));
				oPst.setDouble(6, RandomValues.randomFloat(1000, 9999, 2));
				oPst.setDouble(7, RandomValues.randomFloat(1000, 9999, 2));
				oPst.setString(8, RandomValues.randomString(32));
				oPst.setString(9, RandomValues.randomString(32));
				oPst.setDate(10, RandomValues.randomDate());
				oPst.setDate(11, RandomValues.randomDate());
				oPst.setDate(12, RandomValues.randomDate());
				oPst.setString(13, RandomValues.randomString(32));
				oPst.setString(14, RandomValues.randomString(32));
				oPst.setString(15, RandomValues.randomString(32));
				try {
					oPst.execute();
					nInsertsLineitem++;
					if (nInsertsLineitem == nInsertsLineitemExpected) break;
				} catch (Exception ex) {
					System.out.println(ex.getMessage());
				}
				nPkOrders++;
			}
			if (nInsertsLineitem == nInsertsLineitemExpected) break;
		}	
		
		//Set result vars
		result.put("lineitemRegs", nInsertsLineitem);
		result.put("partnersRegs", nInsertsPartner);
		result.put("world_regionRegs", nInsertsWorldRegion);
		result.put("actual_ordersRegs", nInsertsActualOrder);
		createIndexes();
		return result;
	}
	
	private void createIndexes(){
		execute("ALTER TABLE lineitem SHRINK SPACE");
		execute("CREATE INDEX lineitem_multi ON lineitem(L_ReturnFlag, L_LineStatus, L_ShipDate) PCTFREE 33");
		
		execute("ALTER TABLE world_region SHRINK SPACE");
		execute("CREATE INDEX world_region_date ON world_region(N_Name) PCTFREE 33");
		
		execute("ALTER TABLE partners SHRINK SPACE");
		execute("CREATE INDEX partners_multi ON partners(P_size, P_type) PCTFREE 33");
		
		execute("ALTER TABLE actual_orders SHRINK SPACE");
		execute("CREATE INDEX actual_orders_multi ON actual_orders(C_MktSegment, O_OrderDate) PCTFREE 33");
		execute("CREATE INDEX actual_orders_simple ON actual_orders(O_OrderDate) PCTFREE 33");
	}
	
	private void deleteIndexes(){
		execute("DROP INDEX lineitem_multi");
		execute("DROP INDEX world_region_date");
		execute("DROP INDEX partners_multi");
		execute("DROP INDEX actual_orders_multi");
		execute("DROP INDEX actual_orders_simple");
	}
	
	@Override
	public int query1() {
		String query = "SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty," +
		" sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as "+
		" sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,"+
		" avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) "+
		" as avg_disc, count(*) as count_order"+
		" FROM lineitem"+
		" WHERE l_shipdate <= date '"+lshipdate.toString()+"' "+
		" GROUP BY l_returnflag, l_linestatus"+
		" ORDER BY l_returnflag, l_linestatus";
		
		return executeQuery(query);
		
	}
	
	@Override
	public int query2() {
		String query = "SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment" +
		" FROM partners, world_region" +
		" WHERE p_size = "+psize+" AND p_type like '%"+ptype+"' AND r_name = '"+rname+"' AND s_nationkey = n_nationkey AND ps_supplycost = (SELECT min(ps_supplycost) FROM partners, world_region WHERE s_nationkey = n_nationkey AND r_name = '"+rname+"')"  +
		" ORDER BY s_acctbal desc, n_name, s_name, p_partkey";
		System.out.println(query);
		return executeQuery(query);
	}
	@Override
	public int query3() {
		String query = "SELECT l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, o_orderdate, o_shippriority" +
		" FROM actual_orders, lineitem" +
		" WHERE c_mktsegment = '"+cmktsegment+"' AND l_orderkey = o_orderkey AND o_orderdate < date '"+oorderdate+"' AND l_shipdate > date '"+oorderdate2+"'" +
		" GROUP BY l_orderkey, o_orderdate, o_shippriority" +
		" ORDER BY revenue desc, o_orderdate" ;
		System.out.println(query);
		return executeQuery(query);
	}
	@Override
	public int query4() {
		String query = "SELECT n_name, sum(l_extendedprice * (1 - l_discount)) as revenue" +
		" FROM actual_orders, lineitem, partners, world_region" +
		" WHERE l_orderkey = o_orderkey AND l_partnerkey = p_partnerkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND r_name = '"+rname+"' AND o_orderdate >= date '"+oorderdate2+"' AND o_orderdate < date '"+oorderdate+"' + interval '1' year" +
		" GROUP BY n_name" +
		" ORDER BY revenue desc";
	System.out.println(query);
	return executeQuery(query);
	}
	
	@Override
	public void getDataForQueries() throws SQLException {
		String pAtributes[] = getStrings("partners", new String[]{"P_Size", "P_Type"});
		lshipdate = getDate("lineitem", "L_ShipDate", "MAX");
		psize = pAtributes[0];
		lshipdate = getDate("lineitem", "L_ShipDate", "MAX");
		ptype = pAtributes[1];
		rname = getString("world_region", "r_name");
		oorderdate = getDate("actual_orders", "O_OrderDate", "MAX");
		oorderdate2 = getDate("actual_orders", "O_OrderDate", "MIN");
		cmktsegment = getString("actual_orders", "C_MktSegment");
	}
}
